package top.cardone.data.support.impl;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Table;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharEncoding;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import top.cardone.core.util.action.Action2;
import top.cardone.core.util.func.Func2;
import top.cardone.core.util.func.Func3;
import top.cardone.data.support.ExcelSupport;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by cardone-home-001 on 2016/4/22.
 */
@Log4j2
public class ExcelSupportImpl implements ExcelSupport {
	@Override
	public void readFilePathName(String filePathName, Map<Object, Table<String, String, Object>> configTableMap) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		readFile(new File(filePathName), configTableMap);
	}

	@Override
	public void readFile(File file, Map<Object, Table<String, String, Object>> configTableMap) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		try (InputStream inputStream = new FileInputStream(file)) {
			readInputStream(inputStream, configTableMap);
		}
	}

	@Override
	public void readWorkbook(Workbook workbook, Map<Object, Table<String, String, Object>> configTableMap) throws InstantiationException, IllegalAccessException {
		for (Map.Entry<Object, Table<String, String, Object>> configTableEntry : configTableMap.entrySet()) {
			readWorkbook(workbook, configTableEntry.getKey(), configTableEntry.getValue());
		}
	}

	@Override
	public void readWorkbook(Workbook workbook, Object sheetIndexObject, Table<String, String, Object> configTable) throws InstantiationException, IllegalAccessException {
		Sheet sheet;

		if (sheetIndexObject instanceof Integer) {
			sheet = workbook.getSheetAt((Integer) sheetIndexObject);
		} else {
			sheet = workbook.getSheet(sheetIndexObject.toString());
		}

		if (sheet == null) {
			return;
		}

		Action2<Sheet, Table<String, String, Object>> readSheetAction = (Action2<Sheet, Table<String, String, Object>>) configTable.get("action", "readSheetAction");

		if (readSheetAction != null) {
			readSheetAction.action(sheet, configTable);
		}

		readSheet(sheet, configTable);
	}

	@Override
	public void readSheet(Sheet sheet, Table<String, String, Object> configTable) throws InstantiationException, IllegalAccessException {
		int rowIndex = 0;

		Integer firstRowIndex = (Integer) configTable.get("params", "firstRowIndex");

		if (firstRowIndex == null || firstRowIndex < 0) {
			firstRowIndex = 0;
		}

		Func2<Map<String, Object>, Row, Table<String, String, Object>> readRowFunc = (Func2<Map<String, Object>, Row, Table<String, String, Object>>) configTable.get("func", "readRowFunc");

		Func3<Boolean, Map<String, Object>, Integer, Table<String, String, Object>> validateFunc = (Func3<Boolean, Map<String, Object>, Integer, Table<String, String, Object>>) configTable.get("func", "validateFunc");

		Map<String, Object> columnsMap = configTable.row("columns");

		Map<String, Object> columnTypesMap = configTable.row("columnTypes");

		List<Map<String, Object>> dataList = (List<Map<String, Object>>) configTable.get("data", "dataList");

		if (dataList == null) {
			dataList = Lists.newArrayList();
		}

		List<Map<String, Object>> succeedDataList = (List<Map<String, Object>>) configTable.get("data", "succeedDataList");

		if (succeedDataList == null) {
			succeedDataList = Lists.newArrayList();
		}

		List<Map<String, Object>> errorDataList = (List<Map<String, Object>>) configTable.get("data", "errorDataList");

		if (errorDataList == null) {
			errorDataList = Lists.newArrayList();
		}

		do {
			int rownum = (rowIndex++) + firstRowIndex;

			Row row = sheet.getRow(rownum);

			if (row == null) {
				break;
			}

			Map<String, Object> rowData = (readRowFunc == null) ? readRow(row, columnsMap, columnTypesMap) : readRowFunc.func(row, configTable);

			if (MapUtils.isEmpty(rowData)) {
				continue;
			}

			dataList.add(rowData);

			if (validateFunc == null) {
				continue;
			}

			if (BooleanUtils.isTrue(validateFunc.func(rowData, rownum, configTable))) {
				succeedDataList.add(rowData);

				continue;
			}

			errorDataList.add(rowData);
		} while (true);

		configTable.put("data", "dataList", dataList);
		configTable.put("data", "succeedDataList", succeedDataList);
		configTable.put("data", "errorDataList", errorDataList);
	}

	@Override
	public Map<String, Object> readRow(Row row, Map<String, Object> columns) throws InstantiationException, IllegalAccessException {
		return readRow(row, columns, null);
	}

	@Override
	public Map<String, Object> readRow(Row row, Map<String, Object> columns, Map<String, Object> columnTypes) throws InstantiationException, IllegalAccessException {
		Map<String, Object> map = Maps.newHashMap();

		for (Map.Entry<String, Object> columnEntry : columns.entrySet()) {
			Integer cellnum = (Integer) columnEntry.getValue();

			if (cellnum == null || cellnum < 0) {
				cellnum = 0;
			}

			Cell cell = row.getCell(cellnum);

			if (cell == null) {
				map.put(columnEntry.getKey(), null);

				continue;
			}

			int type = MapUtils.getIntValue(columnTypes, columnEntry.getKey(), cell.getCellType());

			if (type == Cell.CELL_TYPE_BOOLEAN) {
				boolean booleanCellValue = cell.getBooleanCellValue();

				map.put(columnEntry.getKey(), booleanCellValue);
			} else if (type == Cell.CELL_TYPE_NUMERIC) {
				double numericCellValue = cell.getNumericCellValue();

				map.put(columnEntry.getKey(), numericCellValue);
			} else {
				String stringCellValue = cell.getStringCellValue();

				map.put(columnEntry.getKey(), stringCellValue);
			}
		}

		return map;
	}

	@Override
	public void readInputStream(InputStream is, Map<Object, Table<String, String, Object>> configTableMap) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		try (Workbook workbook = WorkbookFactory.create(is)) {
			readWorkbook(workbook, configTableMap);
		}
	}

	@Override
	public void writeFile(String templateFilePath, String outFilePath, Map<Object, Table<String, String, Object>> configTableMap) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		try (Workbook workbook = writeWorkbook(templateFilePath, configTableMap)) {
			try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
				workbook.write(outputStream);

				File outFile = new File(outFilePath);

				if (outFile.exists()) {
					FileUtils.deleteQuietly(outFile);
				}

				FileUtils.writeByteArrayToFile(outFile, outputStream.toByteArray());
			}
		} catch (IOException e1) {
			log.error(e1.getMessage(), e1);
		}
	}

	private Workbook writeWorkbook(String templateFilePath, Map<Object, Table<String, String, Object>> configTableMap) throws IOException, InvalidFormatException, InstantiationException, IllegalAccessException {
		String templateFileExtension = FilenameUtils.getExtension(templateFilePath);

		Workbook workbook;

		if ("xls".equalsIgnoreCase(templateFileExtension)) {
			workbook = new HSSFWorkbook();
		} else {
			workbook = new SXSSFWorkbook(1000);
		}

		try (InputStream inputStream = new FileInputStream(new File(templateFilePath))) {
			try (Workbook templateWorkbook = WorkbookFactory.create(inputStream)) {
				for (Map.Entry<Object, Table<String, String, Object>> configTableEntry : configTableMap.entrySet()) {
					Sheet templateSheet = getTemplateSheet(templateWorkbook, configTableEntry);

					if (templateSheet == null) {
						continue;
					}

					String sheetName = (String) configTableEntry.getValue().get("params", "sheetName");

					Sheet sheet = workbook.createSheet(StringUtils.defaultString(sheetName, templateSheet.getSheetName()));

					Integer firstRowIndex = (Integer) configTableEntry.getValue().get("params", "firstRowIndex");

					if (firstRowIndex == null || firstRowIndex < 0) {
						firstRowIndex = 0;
					}

					try {
						copySheet(sheet, templateSheet, workbook, templateWorkbook, firstRowIndex);
					} catch (Exception e) {
						log.error(e.getMessage(), e);
					}

					writeSheet(sheet, configTableEntry.getValue());
				}
			}
		}

		return workbook;
	}

	/**
	 * 获取模板
	 *
	 * @param templateWorkbook
	 * @param configTableEntry
	 * @return
	 */
	private Sheet getTemplateSheet(Workbook templateWorkbook, Map.Entry<Object, Table<String, String, Object>> configTableEntry) {
		Sheet templateSheet = null;

		Integer templateSheetIndex = (Integer) configTableEntry.getValue().get("params", "templateSheetIndex");

		if (templateSheetIndex != null) {
			templateSheet = templateWorkbook.getSheetAt(templateSheetIndex);
		}

		if (templateSheet != null) {
			return templateSheet;
		}

		String templateSheetName = (String) configTableEntry.getValue().get("params", "templateSheetName");

		if (StringUtils.isNotBlank(templateSheetName)) {
			templateSheet = templateWorkbook.getSheet(templateSheetName);
		}

		if (templateSheet != null) {
			return templateSheet;
		}

		if (configTableEntry.getKey() instanceof Integer) {
			templateSheet = templateWorkbook.getSheetAt((Integer) configTableEntry.getKey());
		} else {
			templateSheet = templateWorkbook.getSheet(configTableEntry.getKey().toString());
		}

		if (templateSheet != null) {
			return templateSheet;
		}

		return templateWorkbook.getSheetAt(0);
	}

	/**
	 * 写入工作薄
	 *
	 * @param workbook       工作薄
	 * @param configTableMap excel配置集合
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	@Override
	public void writeWorkbook(Workbook workbook, Map<Object, Table<String, String, Object>> configTableMap) throws InstantiationException, IllegalAccessException {
		for (Map.Entry<Object, Table<String, String, Object>> configTableEntry : configTableMap.entrySet()) {
			writeWorkbook(workbook, configTableEntry.getKey(), configTableEntry.getValue());
		}
	}

	/**
	 * 写入工作薄
	 *
	 * @param workbook    工作薄
	 * @param configTable excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	@Override
	public void writeWorkbook(Workbook workbook, Object sheetIndexObject, Table<String, String, Object> configTable) throws InstantiationException, IllegalAccessException {
		Sheet sheet;

		if (sheetIndexObject instanceof Integer) {
			sheet = workbook.getSheetAt((Integer) sheetIndexObject);
		} else {
			sheet = workbook.getSheet(sheetIndexObject.toString());
		}

		Action2<Sheet, Table<String, String, Object>> writeSheetAction = (Action2<Sheet, Table<String, String, Object>>) configTable.get("action", "writeSheetAction");

		if (writeSheetAction != null) {
			writeSheetAction.action(sheet, configTable);
		}

		writeSheet(sheet, configTable);
	}

	/**
	 * 写入Sheet
	 *
	 * @param sheet       Sheet
	 * @param configTable excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	@Override
	public void writeSheet(Sheet sheet, Table<String, String, Object> configTable) throws InstantiationException, IllegalAccessException {
		List<Map<String, Object>> writeList = (List<Map<String, Object>>) configTable.get("data", "writeList");

		if (CollectionUtils.isEmpty(writeList)) {
			return;
		}

		int rowIndex = 0;

		Integer firstRowIndex = (Integer) configTable.get("params", "firstRowIndex");

		if (firstRowIndex == null || firstRowIndex < 0) {
			firstRowIndex = 0;
		}

		Map<String, Object> columnsMap = configTable.row("columns");

		for (Map<String, Object> rowData : writeList) {
			int rownum = (rowIndex++) + firstRowIndex;

			Row row = sheet.createRow(rownum);

			writeRow(row, rowData, columnsMap);
		}
	}

	private void writeRow(Row row, Map<String, Object> rowData, Map<String, Object> columnsMap) {
		for (Map.Entry<String, Object> columnEntry : columnsMap.entrySet()) {
			Object value = rowData.get(columnEntry.getKey());

			if (value == null) {
				continue;
			}

			Integer column = (Integer) columnEntry.getValue();

			if (column == null || column < 0) {
				column = 0;
			}

			Cell cell = row.createCell(column);

			if (value instanceof Double) {
				cell.setCellValue((Double) value);
			} else if (value instanceof BigDecimal) {
				cell.setCellValue(value.toString());
			} else if (value instanceof Date) {
				cell.setCellValue((Date) value);
			} else if (value instanceof Integer) {
				cell.setCellValue((Integer) value);
			} else if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			} else if (value instanceof Calendar) {
				cell.setCellValue((Calendar) value);
			} else {
				cell.setCellValue(String.valueOf(value));
			}
		}
	}

	/**
	 * 写入流
	 *
	 * @param response
	 * @param downFilename     下载文件名
	 * @param templateFilePath 模板文件路径
	 * @param configTableMap   excel配置集合
	 * @throws Exception
	 */
	@Override
	public void writeHttpServletResponse(HttpServletResponse response, String downFilename, String templateFilePath, Map<Object, Table<String, String, Object>> configTableMap) throws Exception {
		response.setContentType("application/x-msdownload");
		response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(downFilename, CharEncoding.UTF_8));

		try (Workbook workbook = writeWorkbook(templateFilePath, configTableMap)) {
			try (java.io.OutputStream out = response.getOutputStream()) {
				workbook.write(out);

				out.flush();

				response.flushBuffer();
			}
		}
	}

	/**
	 * 功能：拷贝sheet
	 * 实际调用     copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true)
	 *
	 * @param targetSheet
	 * @param sourceSheet
	 * @param targetWork
	 * @param sourceWork
	 * @param endRowIndex
	 */
	@Override
	public void copySheet(Sheet targetSheet, Sheet sourceSheet, Workbook targetWork, Workbook sourceWork, int endRowIndex) throws Exception {
		copySheet(targetSheet, sourceSheet, targetWork, sourceWork, endRowIndex, true);
	}

	/**
	 * 功能：拷贝sheet
	 *
	 * @param targetSheet
	 * @param sourceSheet
	 * @param targetWork
	 * @param sourceWork
	 * @param endRowIndex
	 * @param copyStyle   boolean 是否拷贝样式
	 */
	@Override
	public void copySheet(Sheet targetSheet, Sheet sourceSheet, Workbook targetWork, Workbook sourceWork, int endRowIndex, boolean copyStyle) throws Exception {
		if (targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null) {
			throw new IllegalArgumentException("调用copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
		}

		if (endRowIndex < 1) {
			return;
		}

		//复制源表中的行
		int maxColumnNum = 0;

		Map<String, CellStyle> styleMap = null;

		if (copyStyle) {
			styleMap = Maps.newHashMap();
		}

		for (int i = sourceSheet.getFirstRowNum(); i < endRowIndex; i++) {
			Row sourceRow = sourceSheet.getRow(i);

			if (sourceRow == null) {
				continue;
			}

			Row targetRow = targetSheet.createRow(i);

			copyRow(targetRow, sourceRow, targetWork, sourceWork, styleMap);

			if (sourceRow.getLastCellNum() > maxColumnNum) {
				maxColumnNum = sourceRow.getLastCellNum();
			}
		}

		//复制源表中的合并单元格
		mergerRegion(targetSheet, sourceSheet);

		//设置目标sheet的列宽
		for (int i = 0; i <= maxColumnNum; i++) {
			targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
		}
	}

	/**
	 * 功能：拷贝row
	 *
	 * @param targetRow
	 * @param sourceRow
	 * @param targetWork
	 * @param sourceWork
	 * @param styleMap
	 */
	@Override
	public void copyRow(Row targetRow, Row sourceRow, Workbook targetWork, Workbook sourceWork, Map<String, CellStyle> styleMap) throws Exception {
		if (targetRow == null || sourceRow == null || targetWork == null || sourceWork == null) {
			throw new IllegalArgumentException("调用copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork都不能为空，故抛出该异常！");
		}

		//设置行高
		targetRow.setHeight(sourceRow.getHeight());

		for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) {
			Cell sourceCell = sourceRow.getCell(i);

			if (sourceCell == null) {
				continue;
			}

			Cell targetCell = targetRow.getCell(i);

			if (targetCell == null) {
				targetCell = targetRow.createCell(i);
			}

			//拷贝单元格，包括内容和样式
			copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap);
		}
	}

	/**
	 * 功能：拷贝cell，依据styleMap是否为空判断是否拷贝单元格样式
	 *
	 * @param targetCell 不能为空
	 * @param sourceCell 不能为空
	 * @param targetWork 不能为空
	 * @param sourceWork 不能为空
	 * @param styleMap   可以为空
	 */
	@Override
	public void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork, Map<String, CellStyle> styleMap) {
		if (targetCell == null || sourceCell == null || targetWork == null || sourceWork == null) {
			throw new IllegalArgumentException("调用copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
		}

		CellStyle sourceCellStyle = sourceCell.getCellStyle();

		//处理单元格样式
		if ((styleMap != null) && (sourceCellStyle != null)) {
			if (targetWork == sourceWork) {
				targetCell.setCellStyle(sourceCellStyle);
			} else {
				Object cellStyleHashCode = sourceCellStyle.hashCode();

				if (cellStyleHashCode != null) {
					String cellStyle = cellStyleHashCode.toString();

					CellStyle targetCellStyle = styleMap.get(cellStyle);

					if (targetCellStyle == null) {
						targetCellStyle = targetWork.createCellStyle();
						targetCellStyle.cloneStyleFrom(sourceCellStyle);
						styleMap.put(cellStyle, targetCellStyle);
					}

					targetCell.setCellStyle(targetCellStyle);
				}
			}
		}

		//处理单元格内容
		switch (sourceCell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				targetCell.setCellValue(sourceCell.getRichStringCellValue());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				targetCell.setCellValue(sourceCell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_BLANK:
				targetCell.setCellType(Cell.CELL_TYPE_BLANK);
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				targetCell.setCellValue(sourceCell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_ERROR:
				targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				targetCell.setCellFormula(sourceCell.getCellFormula());
				break;
			default:
				break;
		}
	}

	/**
	 * 功能：复制原有sheet的合并单元格到新创建的sheet
	 *
	 * @param targetSheet
	 * @param sourceSheet
	 */
	@Override
	public void mergerRegion(Sheet targetSheet, Sheet sourceSheet) throws Exception {
		if (targetSheet == null || sourceSheet == null) {
			throw new IllegalArgumentException("调用mergerRegion()方法时，targetSheet或者sourceSheet不能为空，故抛出该异常！");
		}

		for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);

			CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(), oldRange.getLastColumn());

			targetSheet.addMergedRegion(newRange);
		}
	}

	/**
	 * 写入文件流
	 *
	 * @param is             InputStream
	 * @param configTableMap excel配置集合
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	@Override
	public void writeInputStream(InputStream is, Map<Object, Table<String, String, Object>> configTableMap) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		try (Workbook workbook = WorkbookFactory.create(is)) {
			writeWorkbook(workbook, configTableMap);
		}
	}
}